//
//  SQLDefineHeader.h
//  Estate
//
//  Created by heyong on 16/6/7.
//  Copyright © 2016年 ttsx. All rights reserved.
//
/**
 *  存放项目中的sql语句
 *
 */
#ifndef SQLDefineHeader_h
#define SQLDefineHeader_h

//用户表
#define USER @"USER"
//单位表
#define UNIT @"UNIT"
//项目表
#define PROJECT @"PROJECT"
//项目人员信息表
#define PROJECT_USER @"PROJECT_USER"
//项目单位信息表
#define PROJECT_UNIT @"PROJECT_UNIT"
//位置信息表
#define ADDRESS @"ADDRESS"
//工作记录表
#define WORK_RECORD @"WORK_RECORD"
//工作日记表
#define WORK_DIARY @"WORK_DIARY"
//工作日志表
#define WORK_LOG @"WORK_LOG"
//流程表
#define PROCESS @"PROCESS"
//流程记录表
#define PROCESS_RECORD @"PROCESS_RECORD"
//附件表
#define ATTACHMENT @"ATTACHMENT"
//项目工程概况表
#define PRO_ENGINEER_SURVEY @"PRO_ENGINEER_SURVEY"
//广告表
#define ADVERTISING_PICTURE @"ADVERTISING_PICTURE"
//配置表
#define SYSTEM_CONFIG @"SYSTEM_CONFIG"
//巡检流程表
#define INSPECT_PROCESS @"INSPECT_PROCESS"
//巡检流程记录表
#define INSPECT_PROCESS_RECORD @"INSPECT_PROCESS_RECORD"
//巡检组用户表
#define INSPECT_GROUP_USER @"INSPECT_GROUP_USER"
//巡检组表
#define INSPECT_GROUP @"INSPECT_GROUP"
//用户巡检项表
#define INSPECT_USER_ITEM @"INSPECT_USER_ITEM"
//巡检项实体表
#define INSPECT_ITEM @"INSPECT_ITEM"
//一键汇总表
#define INSPECT_TOTAL_RECORD @"INSPECT_TOTAL_RECORD"
// 见证取样流程表
#define SAMPLE_PROCESS @"SAMPLE_PROCESS"
// 见证取样流程记录表
#define SAMPLE_PROCESS_RECORD @"SAMPLE_PROCESS_RECORD"
// 见证取样code表
#define SAMPLE_CODE @"SAMPLE_CODE"
// 巡视记录表
#define PATROL_RECORD @"PATROL_RECORD"
// 安全记录基本表
#define SAFE_RECORD_BASIC @"SAFE_RECORD_BASIC"
// 安全记录-施工三类人员
#define SAFE_CONSTRUCT @"SAFE_CONSTRUCT"
// 安全记录-特种作业人员
#define SAFE_SPECIAL_TYPE @"SAFE_SPECIAL_TYPE"
// 安全记录-安全巡查
#define SECURITY_PATROL @"SECURITY_PATROL"
// 安全记录-危险性较大工程
#define SAFE_DANGER @"SAFE_DANGER"
// 安全记录-施工设备进场
#define SAFE_CONSTRUCT_DEVICE @"SAFE_CONSTRUCT_DEVICE"

/**
 * 签到签退查询 查询根据人员状态查询  本地版本更新
 */
#define SQL_WORKRECORD_SELECT [NSString stringWithFormat:@"SELECT * FROM WORK_RECORD WHERE RECORD_TYPE IN('RECORD_05','RECORD_06') AND PRO_ID='%@' AND RECORD_USER='%@' and STATE='1' ORDER by CREATE_TIME DESC",PROID,USERID]

///**
// * 工作日记列表页（监理）  查询根据人员状态查询  本地版本更新
// */
#define SQL_WORKREDDIARY_SELECT(proid,userid)  [NSString stringWithFormat:@"SELECT COUNT(*) as count ,substr(CREATE_TIME,1,7) AS condition,substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month FROM WORK_DIARY WHERE PRO_ID='%@'  AND DIARY_USER ='%@' and DIARY_TYPE='DIARY_02' GROUP BY substr(CREATE_TIME,1,7)  ORDER BY date(CREATE_TIME) desc",proid,userid]


/**
 * 工作日记列表页（总监）
 */
#define SQL_WORKREDLOG_SELECT(proid,userid)  [NSString stringWithFormat:@"SELECT COUNT(*) as count ,substr(CREATE_TIME,1,7) AS condition,substr(CREATE_TIME,1,4) as year, substr(CREATE_TIME,6,2) as month FROM WORK_DIARY WHERE PRO_ID='%@'  AND (DIARY_USER ='%@' OR REVIEW_USER='%@') GROUP BY substr(CREATE_TIME,1,7)  ORDER BY date(CREATE_TIME) desc",proid,userid,userid]


/*
 *   工作日记列表页(总监)
 */
#define SQL_WORKREDLOG_Z_SELECT(proid)  [NSString stringWithFormat:@"SELECT count(*) as count, substr(DIARY_TIME,1,4) as year, substr(DIARY_TIME,6,2) as month,substr(DIARY_TIME,1,7) as condition, * FROM WORK_DIARY WHERE  DIARY_TIME >= substr(CREATE_TIME,1,7)  AND PRO_ID = '%@'",proid]


/*
 *  查询所有的记录类型
 *  @param 项目id 用户id 时间
 */

#define SQL_WORKRECORD_ALL(proid,userid,time,type)  [NSString stringWithFormat:@"select ID,TITLE from work_record where date(CREATE_TIME) ='%@' AND pro_id='%@' and RECORD_USER='%@' and RECORD_TYPE=@'%@'",time,proid,userid,type]

/**
 *  查询记录列表页(监理)   查询根据项目类型查询 下个版本更新
 */
#define SQL_WORKREDIARY_SELECT(proid,userid) [NSString stringWithFormat:@" SELECT COUNT(*),date(CREATE_TIME),substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month,substr(CREATE_TIME,9,2) as day,* FROM WORK_RECORD WHERE RECORD_TYPE not in('RECORD_02_01','RECORD_02_02','RECORD_02_03','RECORD_02_04','RECORD_02_05','RECORD_05','RECORD_06','RECORD_02_JJ_01','RECORD_02_JJ_02','RECORD_02_JJ_03','RECORD_02_JJ_04','RECORD_02_JJ_05','RECORD_02_JJ_06') AND PRO_ID='%@' and STATE='1' AND RECORD_USER='%@' AND STATE='1' GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc",proid,userid]

/*
 *  查询记录列表页(总监) 全
 */
#define SQL_WORKREDIARY_SELECT_ZJ(proid) [NSString stringWithFormat:@" SELECT COUNT(*) as count,date(CREATE_TIME),substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month,substr(CREATE_TIME,9,2) as day FROM WORK_RECORD WHERE RECORD_TYPE not in('RECORD_05','RECORD_06') AND PRO_ID='%@' GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc",proid]

/*
 *  查询记录列表页(总监)部分
 */
#define SQL_WORKREDIARY_SELECT_ZJ_CON(proid ,conditions)  [NSString stringWithFormat:@"SELECT COUNT(*) as count,date(CREATE_TIME),substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month,substr(CREATE_TIME,9,2) as day FROM WORK_RECORD WHERE RECORD_TYPE not in('RECORD_05','RECORD_06') AND PRO_ID='%@' AND RECORD_USER IN %@ GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc",proid,conditions]

/*
 *  查询旁站列表页
 **/

#define SQL_WORKRESIDE_SELECT  [NSString stringWithFormat:@"SELECT COUNT(*),date(CREATE_TIME),substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month,substr(CREATE_TIME,9,2) as day FROM WORK_RECORD WHERE RECORD_TYPE in('RECORD_02_01','RECORD_02_02','RECORD_02_03','RECORD_02_04','RECORD_02_05','RECORD_02_JJ_01','RECORD_02_JJ_02','RECORD_02_JJ_03','RECORD_02_JJ_04','RECORD_02_JJ_05','RECORD_02_JJ_06') AND PRO_ID='%@' AND RECORD_USER='%@'  GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc", PROID, USERID]

/*
 *  查询验收列表页(监理)
 **/

#define SQL_WORKACCEPTANCE_SELECT(proid,userid)   [NSString stringWithFormat: @"SELECT COUNT(*),time(CREATE_TIME),date(CREATE_TIME),substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month,substr(CREATE_TIME,9,2) as day FROM PROCESS WHERE PROCESS_TYPE = 3 AND PRO_ID = '%@' AND RECIFY_REVIEW_USERS = '%@' GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc",proid,userid]

/*
 *  查询日志列表页(总监)
 */

#define SQL_WORKLOGJ_SELECT(proid)  [NSString stringWithFormat:@"SELECT COUNT(*) as count, substr(CREATE_TIME,1,7) as condition,substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month,substr(CREATE_TIME,9,2) as day FROM WORK_LOG WHERE  pro_id='%@'  GROUP BY substr(CREATE_TIME,1,7) ORDER BY date(CREATE_TIME) desc",proid]


/*
 *   甲方工作 安全日志
 */

#define SQL_WORKLOGJ_JFAQRZ_SELECT(proid)  [NSString stringWithFormat:@"SELECT COUNT(*) as count, substr(CREATE_TIME,1,7) as condition,substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month,substr(CREATE_TIME,9,2) as day FROM WORK_LOG WHERE  pro_id='%@' and log_type='LOG_02'  GROUP BY substr(CREATE_TIME,1,7) ORDER BY date(CREATE_TIME) desc",proid]

/*
 *   甲方工作 监理日志
 */

#define SQL_WORKLOGJ_JFRZ_SELECT(proid)  [NSString stringWithFormat:@"SELECT COUNT(*) as count, substr(CREATE_TIME,1,7) as condition,substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month,substr(CREATE_TIME,9,2) as day FROM WORK_LOG WHERE  pro_id='%@' and log_type='LOG_01'  GROUP BY substr(CREATE_TIME,1,7) ORDER BY date(CREATE_TIME) desc",proid]


/*
 *  查询跟进列表(监理)
 **/
#define SQL_PROCESS_SELECT_JL(proid,userid)  [NSString stringWithFormat:@"SELECT COUNT(*), date(CREATE_TIME) as time, substr(CREATE_TIME, 1, 4) as year, substr(CREATE_TIME, 6, 2) as month, substr(CREATE_TIME, 9, 2) as day FROM PROCESS WHERE PRO_ID = '%@' AND (PROCESS_PUBLISHERS  = '%@' OR PROCESS_RECIFY_USERS  = '%@') AND PROCESS_TYPE = '2' AND PROCESS_STAGE NOT IN(202,5) GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc",proid,userid,userid]

/*
 *  查询督办列表(总监)
 **/
#define SQL_PROCESS_SELECT_DB(proid)  [NSString stringWithFormat:@"SELECT COUNT(*), date(CREATE_TIME) as time, substr(CREATE_TIME,1,4) as year, substr(CREATE_TIME,6,2) as month, substr(CREATE_TIME,9,2) as day FROM PROCESS WHERE PRO_ID = '%@' AND PROCESS_STAGE NOT IN(202, 5) GROUP BY substr(CREATE_TIME,6,2) ORDER BY date(CREATE_TIME) desc",proid]

/*
 *  查询督办列表(监理) 
 */
#define SQL_PROCESS_SELECT_dbJL(proid,userid) [NSString stringWithFormat:@"SELECT COUNT(*), date(CREATE_TIME) as time, substr(CREATE_TIME,1,4) as year, substr(CREATE_TIME,6,2) as month, substr(CREATE_TIME,9,2) as day FROM PROCESS WHERE PRO_ID = '%@' AND (PROCESS_MASTERS ='%@' OR PROCESS_RECIFY_USERS  = '%@') AND PROCESS_TYPE = '1' AND PROCESS_STAGE NOT IN (5) GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc",proid,userid,userid]

/*
 *  查询待办列表(甲方)
 **/
#define SQL_PROCESS_SELECT_JF(proid)  [NSString stringWithFormat:@"SELECT COUNT(*),date(CREATE_TIME) as time,substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month,substr(CREATE_TIME,9,2) as day FROM PROCESS WHERE PRO_ID='%@' AND ID in(SELECT PROCESS_ID FROM PROCESS_RECORD WHERE SEND_TYPE != '4') AND PROCESS_STAGE NOT IN(5, 202, 401) GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc",proid]

/*
 * 查询历史列表(监理)
 */

#define SQL_PROCESS_SELECT_LSList(proid,masters)  [NSString stringWithFormat:@"SELECT COUNT(*),substr(CREATE_TIME,1,7) AS condition,substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month FROM PROCESS WHERE PRO_ID='%@' AND (PROCESS_MASTERS = '%@' OR PROCESS_PUBLISHERS = '%@') AND PROCESS_STAGE IN (202, 5) GROUP BY substr(CREATE_TIME,1,7) ORDER BY date(CREATE_TIME) desc",proid,masters,masters]



/*
 * 查询历史列表(总监)
 */
#define SQL_PROCESS_SELECT_ZJLSList(proid)  [NSString stringWithFormat:@"SELECT COUNT(*),substr(CREATE_TIME,1,7) AS condition,substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month FROM PROCESS WHERE PRO_ID = '%@' AND PROCESS_STAGE IN (202, 5) GROUP BY substr(CREATE_TIME,1,7) ORDER BY date(CREATE_TIME) desc",proid]

/*
 *  查询验收列表页(施工方)
 **/
#define SQL_WORKACCEPTANCE_SELECT_SGF(userID) @"SELECT COUNT(*), date(CREATE_TIME) FROM PROCESS WHERE ID = (userID) GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc"

/*
 * 查询历史列表(甲方)
 */
#define SQL_PROCESS_SELECT_JFLSList(proid)  [NSString stringWithFormat:@"SELECT COUNT(*),substr(CREATE_TIME,1,7) AS condition,substr(CREATE_TIME,1,4) as year,substr(CREATE_TIME,6,2) as month FROM PROCESS WHERE PRO_ID = '%@' AND PROCESS_STAGE IN (5, 202, 401) AND ID IN (SELECT PROCESS_ID FROM PROCESS_RECORD WHERE SEND_TYPE != '4') GROUP BY substr(CREATE_TIME,1,7) ORDER BY date(CREATE_TIME) desc",proid]

/*
 *  查询验收详情页(施工方)
 **/
//#define SQL_WORKACCEPTANCE_SELECT_SGF @"SELECT COUNT(*), date(CREATE_TIME) FROM PROCESS_RECORD WHERE PROCESS_TYPE = 2 GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc"





/*
 * -----------------------监理二期往下写-------------------------
 **/
/*
 *  巡检组长列表查询 SELECT count(1) from INSPECT_PROCESS where pro_id='' AND INSPECT_GROUP_Id='' GROUP BY substr(CREATE_TIME,1,4)
 
 **/
#define SQL_INSPECTPROCESS_SELECT_LEADER(proId,groupId) [NSString stringWithFormat:@"SELECT count(1) from INSPECT_PROCESS GROUP BY substr(CREATE_TIME,1,4)",proId,groupId]

/*
 *  巡检组长列表查询（根据年份）
 **/
#define SQL_INSPECTPROCESS_SELECT_LEADER_YEAR(YEAR,PROID,GROUP_ID) @"SELECT substr(CREATE_TIME,1,4) as year,* from INSPECT_PROCESS where year='2016' and PRO_ID='%@' AND  INSPECT_GROUP_Id='%@'"


/*
 *  查询见证取样列表(监理)
 **/
#define SQL_SAMPLE_PROCESS_SELECT_JL(proid,publishId)  [NSString stringWithFormat:@"SELECT COUNT(*), substr(CREATE_TIME,1,4) as year, substr(CREATE_TIME,6,2) as month FROM SAMPLE_PROCESS WHERE PRO_ID = '%@' AND SAMPLE_WINESS_USER = '%@' AND ID IN (SELECT PROCESS_ID FROM SAMPLE_PROCESS_RECORD) GROUP BY month ORDER BY CREATE_TIME desc", proid, publishId]

/*
 *  查询见证取样列表(检测方)
 **/
#define SQL_SAMPLE_PROCESS_SELECT_JCF(proid)  [NSString stringWithFormat:@"SELECT COUNT(*), substr(CREATE_TIME,1,4) as year, substr(CREATE_TIME,6,2) as month FROM SAMPLE_PROCESS WHERE PRO_ID = '%@' AND SAMPLE_PROCESS_STATE = '2' AND ID IN(SELECT PROCESS_ID FROM SAMPLE_PROCESS_RECORD) GROUP BY month ORDER BY CREATE_TIME desc", proid]

/**
 *  查询安全列表(监理)
 */
#define SQL_SAFE_LIST_JL(proid, userid) [NSString stringWithFormat:@"SELECT COUNT(*), date(CREATE_TIME), substr(CREATE_TIME, 1, 4) as year, substr(CREATE_TIME, 6, 2) as month, substr(CREATE_TIME, 9, 2) as day, * FROM SAFE_RECORD_BASIC WHERE PRO_ID = '%@' AND RECORD_USER = '%@' GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc", proid, userid]

/**
 *  查询安全列表(总监)
 */
#define SQL_SAFE_LIST_ZJ(proid, userid) [NSString stringWithFormat:@"SELECT COUNT(*), date(CREATE_TIME), substr(CREATE_TIME, 1, 4) as year, substr(CREATE_TIME, 6, 2) as month, substr(CREATE_TIME, 9, 2) as day, * FROM SAFE_RECORD_BASIC WHERE PRO_ID = '%@' AND (RECORD_USER = '%@' OR STATE = '1') GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc", proid, userid]

/**
 *  查询巡视列表(监理)
 */
#define SQL_PATROL_LIST_JL(proid, userid) [NSString stringWithFormat:@"SELECT COUNT(*), date(CREATE_TIME), substr(CREATE_TIME, 1, 4) as year, substr(CREATE_TIME, 6, 2) as month, substr(CREATE_TIME, 9, 2) as day, * FROM PATROL_RECORD WHERE PRO_ID = '%@' AND RECORD_USER = '%@' GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc", proid, userid]

/**
 *  查询巡视列表(总监)
 */
#define SQL_PATROL_LIST_ZJ(proid) [NSString stringWithFormat:@"SELECT COUNT(*), date(CREATE_TIME), substr(CREATE_TIME, 1, 4) as year, substr(CREATE_TIME, 6, 2) as month, substr(CREATE_TIME, 9, 2) as day, * FROM PATROL_RECORD WHERE PRO_ID = '%@' GROUP BY date(CREATE_TIME) ORDER BY date(CREATE_TIME) desc", proid]


#endif /* SQLDefineHeader_h */



